﻿using BCCommon;
using BCEntity.TMS.MarketingCoupon.MarketingCouponStatistics;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BCData.TMS.MarketingCoupon.MarketingCouponStatistics
{
    public class MarketingCouponStatisticsData : IMarketingCouponStatisticsData
    {
        private readonly IDatabaseContext database;
        public MarketingCouponStatisticsData(IDatabaseContext database)
        {
            this.database = database;
        }

        public async Task<MarketingCouponStatisticsEntity> GetStatistics(long agentRecordId,
            bool isCommonBusiness, long? vehicleTransportBusinessConfigId)
        {
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>
            {
                "A.AgentRecordId = ?AgentRecordId"
            };
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            if (isCommonBusiness)
            {
                wheres.Add("A.IsCommonBusiness = 1");
            }
            else
            {
                wheres.Add("A.IsCommonBusiness = 0 AND A.VehicleTransportBusinessConfigId=?VehicleTransportBusinessConfigId");
                parameters.Add("?VehicleTransportBusinessConfigId", vehicleTransportBusinessConfigId.Value, DbType.Int64);
            }
            var sqlQuery = @"  SELECT SUM(A.CouponCount) AS CouponAllCount,
				            SUM(A.CouponAmount * A.CouponCount) AS CouponAllAmount,
				            SUM(A.UsingCount) AS UsingAllCount,
				            SUM(A.CouponAmount * A.UsingCount) AS UsingAllAmount,
				            SUM(A.ReceivedCount)-SUM(A.UsingCount) AS UnusedAllCount,
				            SUM(A.CouponAmount * A.ReceivedCount)-SUM(A.CouponAmount *A.UsingCount) AS UnusedAllAmount,
				            SUM(A.ReceivedCount) AS ReceivedAllCount,
				            SUM(A.CouponAmount * A.ReceivedCount) AS ReceivedAllAmount,
				            SUM(A.RemainCount) AS RemainAllCount,
				            SUM(A.CouponAmount * A.RemainCount) AS RemainAllAmount,
				            SUM(A.InvalidCount) AS InvalidAllCount,
				            SUM(A.CouponAmount * A.InvalidCount) AS InvalidAllAmount
				            FROM marketingcoupontype A   ";
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            var sql = $" {sqlQuery} {whereSql} ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<MarketingCouponStatisticsEntity>(sql, parameters);
        }


        public async Task<IEnumerable<MarketingCouponStatisticsDetailEntity>> GetDetailList(long agentRecordId, bool isCommonBusiness,
            long? vehicleTransportBusinessConfigId,bool? isExpired)
        {
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>()
            {
                "A.AgentRecordId = ?AgentRecordId"
            };
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            if (isCommonBusiness)
            {
                wheres.Add("A.IsCommonBusiness = 1");
            }
            else
            {
                wheres.Add("A.IsCommonBusiness = 0 AND A.VehicleTransportBusinessConfigId=?VehicleTransportBusinessConfigId");
                parameters.Add("?VehicleTransportBusinessConfigId", vehicleTransportBusinessConfigId.Value, DbType.Int64);
            }
            if (isExpired.HasValue)
            {
                if (isExpired.Value)
                {
                    wheres.Add("A.EndDate < CURDATE()");
                }
                else
                {
                    wheres.Add("A.EndDate >= CURDATE()");
                }
            }
            var sqlQuery = @" SELECT A.MarketingCouponTypeId,A.CouponName,A.CouponAmount,
                                A.CouponCount,
                                A.RemainCount,
                                A.UsingCount,
                                A.ReceivedCount,
                                A.InvalidCount
                                 FROM marketingcoupontype A  ";
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            var sql = $" {sqlQuery} {whereSql} ";
            return await this.database.Slave.QueryAsync<MarketingCouponStatisticsDetailEntity>(sql, parameters);
        }


        public async Task<int> GetReceiveCount(long marketingCouponTypeId, DateTime? startDate, DateTime? endDate)
        {
            var sqlQuery = @" SELECT COUNT(A.MarketingCouponReceiveId) FROM marketingcouponreceive A ";
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>()
            {
                "A.MarketingCouponTypeId = ?MarketingCouponTypeId"
            };
            parameters.Add("?MarketingCouponTypeId", marketingCouponTypeId, DbType.Int64);
            if (startDate.HasValue)
            {
                wheres.Add("A.CreateTime >=?StartDate");
                parameters.Add("?StartDate", startDate.Value.Date, DbType.Date);
            }
            if (endDate.HasValue)
            {
                wheres.Add("A.CreateTime <?EndDate");
                parameters.Add("?EndDate", endDate.Value.AddDays(1).Date, DbType.Date);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            var sql = $" {sqlQuery} {whereSql} ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<int>(sql, parameters);
        }

        public async Task<int> GetUsingCount(long marketingCouponTypeId, DateTime? startDate, DateTime? endDate)
        {
            var sqlQuery = @" SELECT COUNT(A.MarketingCouponUsingId) FROM marketingcouponusing A ";
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>()
            {
                "A.MarketingCouponTypeId = ?MarketingCouponTypeId"
            };
            parameters.Add("?MarketingCouponTypeId", marketingCouponTypeId, DbType.Int64);
            if (startDate.HasValue)
            {
                wheres.Add("A.CreateTime >=?StartDate");
                parameters.Add("?StartDate", startDate.Value.Date, DbType.Date);
            }
            if (endDate.HasValue)
            {
                wheres.Add("A.CreateTime <?EndDate");
                parameters.Add("?EndDate", endDate.Value.AddDays(1).Date, DbType.Date);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            var sql = $" {sqlQuery} {whereSql} ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<int>(sql, parameters);
        }

        public async Task<(IEnumerable<MarketingCouponStatisticsDetailEntity>, int)> GetDetailQuery(long agentRecordId, bool isCommonBusiness, 
            long? vehicleTransportBusinessConfigId, bool? isExpired, int pageIndex, int pageSize)
        {
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>()
            {
                "A.AgentRecordId = ?AgentRecordId"
            };
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            if (isCommonBusiness)
            {
                wheres.Add("A.IsCommonBusiness = 1");
            }
            else
            {
                wheres.Add("A.IsCommonBusiness = 0 AND A.VehicleTransportBusinessConfigId=?VehicleTransportBusinessConfigId");
                parameters.Add("?VehicleTransportBusinessConfigId", vehicleTransportBusinessConfigId.Value, DbType.Int64);
            }
            if (isExpired.HasValue)
            {
                if (isExpired.Value)
                {
                    wheres.Add("A.EndDate < CURDATE()");
                }
                else
                {
                    wheres.Add("A.EndDate >= CURDATE()");
                }
            }
            var sqlQuery = @" SELECT A.MarketingCouponTypeId,A.CouponName,A.CouponAmount,
                                A.CouponCount,
                                A.RemainCount,
                                A.UsingCount,
                                A.ReceivedCount,
                                A.InvalidCount
                                 FROM marketingcoupontype A  ";
            var sqlCount = @" SELECT COUNT(A.MarketingCouponTypeId) FROM marketingcoupontype A ";
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            var sql = $@" {sqlQuery} {whereSql} Order By A.MarketingCouponTypeId DESC 
                        LIMIT { (pageIndex - 1) * pageSize}, { pageSize};{ sqlCount}{ whereSql} ";
            return await this.database.Slave.QueryPageableListAsync<MarketingCouponStatisticsDetailEntity>(sql, parameters);
        }

        public async Task<MarketingCouponStatisticsEntity> GetMarketingCouponAgentStatistics(long agentRecordId, 
            bool? isCommonBusiness, long? vehicleTransportBusinessConfigId, DateTime? startDate, DateTime? endDate)
        {
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>
            {
                "A.AgentRecordId = ?AgentRecordId"
            };
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            if (isCommonBusiness.HasValue)
            {
                if (isCommonBusiness.Value)
                {
                    wheres.Add("A.IsCommonBusiness = 1");
                }
                else
                {
                    wheres.Add("A.IsCommonBusiness = 0");
                }
            }
            if (vehicleTransportBusinessConfigId.HasValue)
            {
                wheres.Add(" A.VehicleTransportBusinessConfigId=?VehicleTransportBusinessConfigId");
                parameters.Add("?VehicleTransportBusinessConfigId", vehicleTransportBusinessConfigId.Value, DbType.Int64);
            }
            if (startDate.HasValue)
            {
                wheres.Add("A.StartDate >=?StartDate");
                parameters.Add("?StartDate", startDate.Value.Date, DbType.Date);
            }
            if (endDate.HasValue)
            {
                wheres.Add("A.EndDate <?EndDate");
                parameters.Add("?EndDate", endDate.Value.AddDays(1).Date, DbType.Date);
            }
            var sqlQuery = @"  SELECT SUM(A.CouponCount) AS CouponAllCount,
				                SUM(A.CouponAmount * A.CouponCount) AS CouponAllAmount,
				                SUM(A.UsingCount) AS UsingAllCount,
				                SUM(A.CouponAmount * A.UsingCount) AS UsingAllAmount,
				                SUM(A.ReceivedCount)- SUM(A.UsingCount) AS UnusedAllCount,
				                SUM(A.CouponAmount * A.ReceivedCount)- SUM(A.CouponAmount *A.UsingCount) AS UnusedAllAmount,
				                SUM(A.ReceivedCount) AS ReceivedAllCount,
				                SUM(A.CouponAmount * A.ReceivedCount) AS ReceivedAllAmount,
				                SUM(A.RemainCount) AS RemainAllCount,
				                SUM(A.CouponAmount * A.RemainCount) AS RemainAllAmount,
				                SUM(A.InvalidCount) AS InvalidAllCount,
				                SUM(A.CouponAmount * A.InvalidCount) AS InvalidAllAmount
				                FROM marketingcoupontype A   ";
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            var sql = $" {sqlQuery} {whereSql} ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<MarketingCouponStatisticsEntity>(sql, parameters);
        }

        public async Task<IEnumerable<MarketingCouponTransportBusinessAmountEntity>> GetMarketingCouponTransportBusinessAmount(long agentRecordId,
    bool? isCommonBusiness, long? vehicleTransportBusinessConfigId, DateTime? startDate, DateTime? endDate)
        {
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>
            {
                "A.AgentRecordId = ?AgentRecordId"
            };
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            if (isCommonBusiness.HasValue)
            {
                if (isCommonBusiness.Value)
                {
                    wheres.Add("A.IsCommonBusiness = 1");
                }
                else
                {
                    wheres.Add("A.IsCommonBusiness = 0");
                }
            }
            if (vehicleTransportBusinessConfigId.HasValue)
            {
                wheres.Add(" A.VehicleTransportBusinessConfigId=?VehicleTransportBusinessConfigId");
                parameters.Add("?VehicleTransportBusinessConfigId", vehicleTransportBusinessConfigId.Value, DbType.Int64);
            }
            var sqlQuery = @"  SELECT 
                            SUM(A.CouponCount*A.CouponAmount) AS MarketingCouponAmount,
                            A.VehicleTransportBusinessConfigId,
                            IFNULL(A.TransportBusiness,'通用') AS TransportBusiness,
                            SUM(A.CouponCount) AS CouponCount,
                            SUM(A.ReceivedCount) AS ReceivedCount,
                            SUM(A.RemainCount) AS RemainCount,
                            SUM(A.InvalidCount) AS InvalidCount,
                            A.IsCommonBusiness
                              FROM marketingcoupontype A   ";
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            var sql = $" {sqlQuery} {whereSql} GROUP BY A.VehicleTransportBusinessConfigId";
            return await this.database.Slave.QueryAsync<MarketingCouponTransportBusinessAmountEntity>(sql, parameters);
        }
    }
}
